#!/usr/bin/env python3

import psutil
import time
import pymysql as mysqldb

def db():
    db = mysqldb.connect(user='root', passwd="123123", host="localhost")
    db.autocommit(True)
    cur = db.cursor()

    '''show database'''
    cur.execute("show databases;")
    a = str(cur.fetchall())
    print ("the database name as follow\n %s" %a)

    '''create database network'''
    cur.execute("create database if not exists network;")
    b = cur.fetchall()
    print (b)

    '''create table in network and name net_io'''
    table_sql = '''create table if not exists network.net_io(time varchar(20),sent int,recv int,primary key(time));'''
    cur.execute(table_sql)
    c = cur.fetchall()
    print (c)
db()

'''definition sent,recv data'''
sql_insert = 'insert into network.net_io(time,sent,recv) values({0},{1},{2})'
sent_old,recv_old,*rest = psutil.net_io_counters()
sent_old = 0
recv_old = 0

def sys_netio():
    db = mysqldb.connect(user='root', passwd="123123", host="localhost")
    db.autocommit(True)
    cur = db.cursor()
    i = 0
    flag = 1

    '''loop'''
    while True:
        global sent_old, recv_old 
        time.sleep(1)
        timestamp = int(time.time())
        sent_new,recv_new,*rest = psutil.net_io_counters()
        sent,recv = sent_new - sent_old,recv_new - recv_old
        sent_old,recv_old = sent_new,recv_new
        cur.execute(sql_insert.format(timestamp, sent, recv))
        if flag:
            flag = 0
            continue
        if (i % 10) == 0:
            print ("\n\033[1;31mtimestamp ----- sent -- recv\033[0m")
        i = i + 1
        print("%s\t\033[32m%s\033[0m\t\033[34m%s\033[0m" % (timestamp, sent, recv))
sys_netio()
